
library(dplyr)
data<-readRDS("table1_data.rds")

services <- c("Waste_treatment", "Waste_collection",  
               "Transport", "Fire","Libraries", "Civil_protection", "Libraries", "Drinking_water",  
               "Sewer")



  yearly_counts <- data %>% filter(population>500)%>%
    group_by(year) %>%
    summarise(
      distinct_municipalities = n_distinct(id),
      .groups = 'drop'
    ) %>%
    arrange(year)

overall<-sum(yearly_counts$distinct_municipalities)


# column 1 all services except waste treatment and fire services

data_final_imc_clean_2011 <- data %>% 
              filter(imc_dummy==1)%>%filter(population>500)%>%filter(year==2011)%>%
              group_by(service) %>% tally()%>% 
              filter(service %in% services)

data_final_imc_clean_2011$overall_share_2011<-(data_final_imc_clean_2011$n/588)*100

print(data_final_imc_clean_2011)

# column 1 for waste treatment and fire services

data_final_imc_clean_2014 <- data %>% 
              filter(imc_dummy==1)%>%filter(population>500)%>%filter(year==2014)%>%
              group_by(service) %>% tally()%>% 
              filter(service %in% services)

data_final_imc_clean_2014$overall_share_2014<-(data_final_imc_clean_2014$n/608)*100
print(data_final_imc_clean_2014)

# column 2

data_final_imc_clean_2022 <- data %>% 
              filter(imc_dummy==1)%>%filter(population>500)%>%filter(year==2022)%>%
              group_by(service) %>% tally()%>% 
              filter(service %in% services)

data_final_imc_clean_2022$overall_share_2022<-(data_final_imc_clean_2022$n/613)*100

print(data_final_imc_clean_2022)
# column4 

 yearly_counts <- data %>% 
    group_by(year) %>%
    summarise(
      distinct_municipalities = n_distinct(id),
      .groups = 'drop'
    ) %>%
    arrange(year)


data_final_imc_clean_2022 <- data %>% 
              filter(imc_dummy==1)%>%filter(year==2022)%>%
              group_by(service) %>% tally()%>% 
              filter(service %in% services)

data_final_imc_clean_2022$overall_share_2022<-(data_final_imc_clean_2022$n/942)*100
print(data_final_imc_clean_2022)



